import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Component;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.Locale;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.SwingConstants;
import javax.swing.filechooser.FileFilter;
import javax.swing.table.DefaultTableCellRenderer;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

//import TableDeneme.DecimalFormatRenderer;

public class Table extends JFrame {

	QueryTableModel qtm;
	JTable table;
	JScrollPane scrollpane;
	JPanel p1, p2;
	JButton jb, jxl;
	ListSelectionModel rowSM;
	Font f1 = new Font("Arial", Font.BOLD, 14);

	JLabel lblid, lblname;
	JTextField txtid;

	String gen;
	ResultSet rs = null;
	Connection con = null;
	Statement stmt = null;
	// AN
	// private NumberFormat numberFormat= new SimpleNumberFormat("###.###.###,##");
	public static void main(String[] args) {
		Table td = new Table();

	}
	public Table() {
		super("Báo cáo tài chính của Doanh Nghiệp");
		qtm = new QueryTableModel();
		table = new JTable(qtm);
		scrollpane = new JScrollPane(table);
		p1 = new JPanel();
		jb = new JButton("Load Adempiere");

		jxl = new JButton("Export Excel");

		p1.add(jb);
			
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		getContentPane().add(p1, BorderLayout.NORTH);
		getContentPane().add(scrollpane, BorderLayout.CENTER);
		setExtendedState(JFrame.MAXIMIZED_BOTH);
		
		//addWindowListener(new BasicWindowMonitor());
		
		setSize(1200, 900);
		setVisible(true);
		jb.setFont(f1);

		lblid = new JLabel("u_fheader_id = ");
		txtid = new JTextField(15);

		lblname = new JLabel("Tên Doanh Nghiệp = ");

		lblid.setBounds(300, 140, 100, 20);
		lblname.setBounds(300, 180, 100, 20);

		txtid.setBounds(400, 140, 100, 20);

		// p1.add(lblid);

		txtid.setText("134325");  //134325   //133748
		qtm.setU_frheader_id(new Integer(txtid.getText()).intValue());
		
		jxl.setEnabled(false);
		p1.add(jxl);
		p1.add(txtid);
		p1.add(lblname);

		jxl.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				// txtid.setText("Vănvq");
				
				JFileChooser chooser = new JFileChooser();
				chooser.setFileFilter(new FileFilter() {

					@Override
					public boolean accept(File file) {
						if (file.isDirectory()) {
							return true;
						}
						if (file.isFile() && file.getName().endsWith(".xls")) {
							return true;
						}
						return false;
					}

					@Override
					public String getDescription() {
						return "Excel (*.xls)";
					}
				});
				chooser.setDialogTitle("Save excel file to...");

				int state = chooser.showSaveDialog(null);
				File file = chooser.getSelectedFile();

				if (file != null && state == JFileChooser.APPROVE_OPTION) {
					String fileName = file.getAbsolutePath();
					if (!fileName.endsWith(".xls")) {
						fileName += ".xls";
						file = new File(fileName);
					}

					try {
			 

						HSSFWorkbook workbook = new HSSFWorkbook();
						
						
				        HSSFFont boldFont = workbook.createFont();
				        boldFont.setColor(HSSFColor.BLUE.index);
				        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				        boldFont.setFontHeightInPoints((short)11);


				        

				        HSSFCellStyle headerStyle = workbook.createCellStyle();
				        headerStyle.setFont(boldFont);
				        headerStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.index);
						
				        HSSFFont boldFontName = workbook.createFont();
				        //boldFontName.setColor(HSSFColor.index);
				        boldFontName.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				        boldFontName.setFontHeightInPoints((short)15);

				        HSSFCellStyle nameStyle = workbook.createCellStyle();
				        nameStyle.setFont(boldFontName);
				        nameStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.index);
				        
				        
						//HSSFCellStyle headerStyle = workbook.createCellStyle();
						//headerStyle.setFillForegroundColor(new HSSFColor.BLUE()
						//		.getIndex());
						//headerStyle.setFillForegroundColor(new HSSFColor.RED().getIndex());

						HSSFSheet firstSheet = workbook
								.createSheet("Sheet 1");
						HSSFCell cellA = null;
						HSSFCell cellB = null;
						HSSFCell cellC = null;
						HSSFCell cellD = null;
						
						
						
						Object cellValue = null;
						
						int row = 0;
						
						HSSFRow rowB = firstSheet.createRow(row++);

						
						
						cellB = rowB.createCell(1);						
						cellB.setCellValue("Bản báo cáo tài chính của : " + lblname.getText());
						cellB.setCellStyle(nameStyle);
						

						HSSFRow rowC = firstSheet.createRow(row++);						
						cellC = rowC.createCell(2);
						HSSFRow rowD = firstSheet.createRow(row++);						
						cellD = rowD.createCell(2);
						
						
						HSSFRow rowA = firstSheet.createRow(row++);
						
						// Fist Row
						for (int i = 0; i < table.getColumnCount(); i++) {
							cellA = rowA.createCell(i);
							String columnName = table.getColumnName(i);
							cellA.setCellValue(new HSSFRichTextString(columnName));
							cellA.setCellStyle(headerStyle);
						}
						

						
						HSSFFont cellFont = workbook.createFont();
						//cellFont.setColor(HSSFColor.RED.index);
						
						//cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
						cellFont.setFontHeightInPoints((short)11);


				        HSSFCellStyle cellStyle = workbook.createCellStyle();
				        cellStyle.setFont(cellFont);
				        cellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
				        
				        
				        
				        //
				        
				        DecimalFormat format= new DecimalFormat("###,###.##");
						format.setDecimalFormatSymbols(new DecimalFormatSymbols(Locale.GERMAN));
						
						//
						for (int i = 0; i < table.getRowCount(); i++) {
							rowA = firstSheet.createRow(row++);
							for (int j = 0; j < table.getColumnCount(); j++) {
								HSSFCell cell = rowA.createCell(j);
								Object obj = table.getValueAt(i, j);
								String value = obj == null ? "" : obj
										.toString();
								System.out.println("Value =" + value);
								if (j==1) {
									
									
								}
								firstSheet.autoSizeColumn(j);
								// SO DU DAU KY CUOI KY (NUMERIC VALUE)
								if (j == 2 || j == 3) {
									try {
										Number num =format.parse(value);
										//String value2 = value.replace(",", "");
										//Double d = Double.parseDouble(value2);
										Double d= num.doubleValue();
										cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
									   //new org.apache.poi.hssf.usermodel.HSSF
										if( 130 < d && d < 150)  {
											// cell.setCellStyle(cellStyle);
										}else {
											cell.setCellStyle(cellStyle);											
										}										
										cell.setCellValue( d );
									} catch (Exception e1) {
										e1.printStackTrace();
									}
								} else {// CHARACTOR VALUE

									
									cell.setCellType(HSSFCell.CELL_TYPE_STRING);
									//cellStyle.setAlignment((short)cellStyle.ALIGN_CENTER);
									cell.setCellValue(new HSSFRichTextString(value));
								}
								
									
								
							}
						}
						
						FileOutputStream out = new FileOutputStream(file);
						workbook.write(out);
						out.close();

					} catch (Exception ex) {
						JOptionPane.showMessageDialog(null, "Export bị lỗi...");
						ex.printStackTrace();
					}
				}

			}

		});

		jb.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				// qtm.setHostURL("jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=281;DBQ=db1.mdb");
				// Access DB
				// qtm.setDriverName("sun.jdbc.odbc.JdbcOdbcDriver");
				// qtm.setHostURL("jdbc:odbc:mydatasource;UID=admin");
				// qtm.setQuery("select * from soz");

				// Posgest !!!!
				qtm.setU_frheader_id(new Integer(txtid.getText()));
				if (e.getActionCommand() == "Load Adempiere")
					try {
						qtm.setDriverName("org.postgresql.Driver");
						qtm.setHostURL("jdbc:postgresql://localhost/adempiere?user=adempiere&password=adempiere");
						qtm.setQuery("select bs.code,bs.name, bl.so_dauky,bl.so_cuoiky,bl.thuyetminh  from u_bscode bs, u_balancesheet bl where bs.u_bscode_id=bl.u_bscode_id and bl.u_frheader_id="
								+ qtm.getU_frheader_id() + " order by bs.code");
						// qtm.setQuery("select id,code,name from cpms_slc_unicode");
						// getData();
						
						 

						table.getColumnModel()
								.getColumn(2)
								.setCellRenderer(
										new DecimalFormatRenderer(
												"###,###,###.00"));
						table.getColumnModel()
								.getColumn(3)
								.setCellRenderer(
										new DecimalFormatRenderer(
												"###,###,###.00"));

						// table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

						// table.getColumnModel().getColumn(0).setMaxWidth(7700);
						table.getColumnModel().getColumn(0).setMinWidth(100);
						table.getColumnModel().getColumn(1).setMinWidth(400);
						table.getColumnModel().getColumn(2).setMinWidth(280);
						table.getColumnModel().getColumn(3).setMinWidth(280);
						table.getColumnModel().getColumn(4).setMinWidth(120);

						alignRight(table, 0, "C");
						alignRight(table, 2, "R");
						alignRight(table, 3, "R");
						alignRight(table, 4, "C");

						
						if (table.getColumnCount() >0) {
//							getData();
							jxl.setEnabled(true);
						} else
						{
							jxl.setEnabled(false);
							lblname.setText("");
						}
						
					} catch (Exception ex) {
						ex.printStackTrace();

					}

			}

			private void alignRight(JTable table, int column, String s) {

				// DefaultTableCellRenderer rightRenderer = newname	TableDeneme.java
				// DefaultTableCellRenderer();
				// DefaultTableCellRenderer rightRenderer =
				// (DefaultTableCellRenderer) table
				// .getCellRenderer(0, column);

				DefaultTableCellRenderer rightRenderer = (DefaultTableCellRenderer) table
						.getColumnModel().getColumn(column).getCellRenderer();
				if (rightRenderer == null) {
					rightRenderer = new DefaultTableCellRenderer();
				}
				if (s == null) {
					return;
				}
				// System.out.println("SS=" + s);
				if (s.equals("R")) {
					rightRenderer.setHorizontalAlignment(SwingConstants.RIGHT);
				}
				if (s.equals("L")) {
					rightRenderer.setHorizontalAlignment(SwingConstants.LEFT);
				}
				if (s.equals("C")) {
					rightRenderer.setHorizontalAlignment(SwingConstants.CENTER);
				}// */
				table.getColumnModel().getColumn(column)
						.setCellRenderer(rightRenderer);

			}
		});

	}
	

	class DecimalFormatRenderer extends DefaultTableCellRenderer {
		// NumberFormat format = null;
		// Locale loc = new Locale("ge", "GE");
		// NumberFormat currFmt = new NumberFormat
		DecimalFormat format = null;

		// double amt = 123456.78;
		// System.out.println(currFmt.format(amt));

		public DecimalFormatRenderer(String formatString) {
			// this.formatString = formatString;
			format = new DecimalFormat(formatString);
			format.setDecimalFormatSymbols(new DecimalFormatSymbols(Locale.GERMAN));
		}

		@Override
		public Component getTableCellRendererComponent(JTable table,
				Object value, boolean isSelected, boolean hasFocus, int row,
				int column) {

			JLabel label = (JLabel) super.getTableCellRendererComponent(table,
					value, isSelected, hasFocus, row, column);

			if (value instanceof java.lang.Number) {
				Number newValue = (Number) value;
				String text = format.format(newValue.doubleValue());
				label.setText(text);
			} else if (value instanceof String) {
				// System.out.println("String");

				Number newValue = null;
				try {

					newValue = Double.parseDouble((String) value);

					String text = format.format(newValue.doubleValue());
					label.setText(text);
					

					if ( row%2  == 0) {

						//setBackground(Color.lightGray);

					} else {
						setBackground(Color.white);
						setForeground(Color.black);
					}

				} catch (Exception e) {
				}

			}

			return label;

		}
	}
}